package com.in.universityinfo.database;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.in.universityinfo.constant.ConstantVariables;
import com.in.universityinfo.model.UniversitiesAdapterModel;

public class UniversityDatasource {

	private SQLiteDatabase database;
	private MyDatabaseHelper dbHelper;

	public UniversityDatasource(Context context) {

		// Copy database from asset folder
		File dbFile = context.getDatabasePath(ConstantVariables.DATABASE_NAME);

		if (!dbFile.exists()) {
			copyDatabase(context);
		}

		dbHelper = new MyDatabaseHelper(context);
	}

	public void open() {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {

		dbHelper.close();
	}

	public List<String> getStates() {
		List<String> states = new ArrayList<String>();

		String sqlStr = "SELECT DISTINCT TRIM("
				+ ConstantVariables.COLUMN_STATE + ") AS "
				+ ConstantVariables.COLUMN_STATE + " FROM "
				+ ConstantVariables.TABLE_UNIVERSITIES + ";";
		Cursor cursor = database.rawQuery(sqlStr, null);

		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String state = cursor.getString(cursor
						.getColumnIndex(ConstantVariables.COLUMN_STATE));
				states.add(state);
				cursor.moveToNext();
			}
		}

		cursor.close();
		return states;
	}

	public List<String> getCities(String state) {
		List<String> cities = new ArrayList<String>();

		String sqlStr = "SELECT DISTINCT TRIM(" + ConstantVariables.COLUMN_CITY
				+ ") AS " + ConstantVariables.COLUMN_CITY + " FROM "
				+ ConstantVariables.TABLE_UNIVERSITIES + "" + " WHERE "
				+ ConstantVariables.COLUMN_STATE + " = '" + state + "';";
		Cursor cursor = database.rawQuery(sqlStr, null);

		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String city = cursor.getString(cursor
						.getColumnIndex(ConstantVariables.COLUMN_CITY));
				cities.add(city);
				cursor.moveToNext();
			}
		}

		cursor.close();
		return cities;
	}

	/**
	 * This method get all universities corresponded to the selected state and
	 * city
	 * 
	 * @param state
	 * @return List of Universities
	 */
	public List<String> getUniversities(String state, String city) {
		List<String> universities = new ArrayList<String>();

		String sqlStr = "SELECT DISTINCT TRIM("
				+ ConstantVariables.COLUMN_UNIVERSITY + ") AS "
				+ ConstantVariables.COLUMN_UNIVERSITY + " FROM "
				+ ConstantVariables.TABLE_UNIVERSITIES + " WHERE "
				+ ConstantVariables.COLUMN_STATE + " = '" + state + "' AND "
				+ ConstantVariables.COLUMN_CITY + " = '" + city + "';";
		Cursor cursor = database.rawQuery(sqlStr, null);

		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				String universitiy = cursor.getString(cursor
						.getColumnIndex(ConstantVariables.COLUMN_UNIVERSITY));
				universities.add(universitiy);
				cursor.moveToNext();
			}
		}

		cursor.close();
		return universities;
	}

	public List<UniversitiesAdapterModel> getUniversityInformation(
			String state, String city, String university) {
		List<UniversitiesAdapterModel> universities = new ArrayList<UniversitiesAdapterModel>();

		String sqlStr = "SELECT * FROM " + ConstantVariables.TABLE_UNIVERSITIES
				+ " WHERE " + ConstantVariables.COLUMN_STATE + " = '" + state
				+ "' AND " + ConstantVariables.COLUMN_CITY + " = '" + city
				+ "' AND " + ConstantVariables.COLUMN_UNIVERSITY + " = '"
				+ university + "';";

		Cursor cursor = database.rawQuery(sqlStr, null);

		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {

				UniversitiesAdapterModel universityModel = new UniversitiesAdapterModel();
				universityModel
						.setUniversity(cursor
								.getString(
										cursor.getColumnIndex(ConstantVariables.COLUMN_UNIVERSITY))
								.trim());
				universityModel.setState(cursor.getString(
						cursor.getColumnIndex(ConstantVariables.COLUMN_STATE))
						.trim());
				universityModel.setCity(cursor.getString(
						cursor.getColumnIndex(ConstantVariables.COLUMN_CITY))
						.trim());
				universityModel.setAddress(cursor.getString(cursor
						.getColumnIndex(ConstantVariables.COLUMN_ADDRESS)));
				universityModel.setPhone(cursor.getString(cursor
						.getColumnIndex(ConstantVariables.COLUMN_PHONE)));
				universityModel.setEmail(cursor.getString(cursor
						.getColumnIndex(ConstantVariables.COLUMN_EMAIL)));
				universityModel.setWebsite(cursor.getString(cursor
						.getColumnIndex(ConstantVariables.COLUMN_WEBSITE)));

				universities.add(universityModel);

				cursor.moveToNext();
			}
		}
		cursor.close();
		return universities;
	}

	private void copyDatabase(Context context) {
		InputStream mInput;
		try {
			mInput = context.getAssets().open(ConstantVariables.DATABASE_NAME);
			String outFileName = "data/data/" + context.getPackageName()
					+ "/databases/" + ConstantVariables.DATABASE_NAME;
			File mfile = new File(outFileName);
			if (!mfile.exists()) {
				File dir = new File("data/data/" + context.getPackageName()
						+ "/databases/");
				dir.mkdir();
				mfile.createNewFile();
			}

			OutputStream mOutput = new FileOutputStream(outFileName);
			byte[] buffer = new byte[1024];
			int length;
			while ((length = mInput.read(buffer)) > 0) {
				mOutput.write(buffer, 0, length);
			}

			mOutput.flush();
			mOutput.close();
			mInput.close();
		} catch (IOException e) {

			e.printStackTrace();
		}
	}
}
